import java.sql.*;


public class LotteryDBManagement {

	static String url = "jdbc:mysql://localhost:3306/";
	static String db = "lottery";
	static String driver = "com.mysql.jdbc.Driver";
	static Connection conn = null;
	
	
	public LotteryDBManagement() throws ClassNotFoundException, SQLException{
		//connect to database
		Class.forName(driver);
		conn = DriverManager.getConnection(url+db,"root","root");
		  		
	}
	
	public String PurchaseLottery(String LotteryInformation, String AKS) {
		try{
				  
			try{
				//get ReceiptNo
				String sqlCommand = "Select UUID() as ReceiptNo";
				Statement stm = conn.createStatement();
				
				ResultSet rs = stm.executeQuery(sqlCommand);
				
				String receiptNo = "";
						
				if(rs.next()){
					receiptNo = rs.getString("ReceiptNo");
					
					sqlCommand = "Insert into lotterydetail(ReceiptNo,PurchaseDate,LotteryInformation,lotterydetail) " +
						  		"values(?,sysdate(),?,?)";
					  
					PreparedStatement prepareStm;
					
					prepareStm = conn.prepareStatement(sqlCommand);
					prepareStm.setString(1, receiptNo);
					prepareStm.setString(2, LotteryInformation);
					prepareStm.setString(3, AKS);
					
					int rowEffect = prepareStm.executeUpdate();
					
					if(rowEffect > 0){
						return receiptNo;
					}
				}
				else{
					throw new Exception("Generate receipt number error.");
				}
				  
			}
			catch (SQLException s){
				System.out.println("SQL statement is not executed!");
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		
		return "";		
	}
	
	public boolean GenerateResult(){
		try {
			AESEncryptDecrypt aesTest = new AESEncryptDecrypt();
			aesTest.generateKeyAES();
			
			String testResult = aesTest.encryptMessage("12,3,2,34,6,40");
			
			String sqlCommand = "Update lotteryResult set Result=? Where LottoID=1";
			
			
			PreparedStatement prepareStm;
			
			prepareStm = conn.prepareStatement(sqlCommand);
			prepareStm.setString(1, testResult);
			
			
			int rowEffect = prepareStm.executeUpdate();
			
			if(rowEffect > 0){
				System.out.println("Update result success.");
				return true;
			}
			else{
				System.out.println("Update fail!!.");
				return false;
			}
			
			
			//connect to database
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

}
